package server.database;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneOffset;

import com.sun.beans.decoder.DocumentHandler;
import org.json.JSONObject;
import server.entity.*;



import org.json.JSONArray;


public class RegistrationDatabase{

 
    private static RegistrationDatabase dbInstance = null;


    public static synchronized RegistrationDatabase getInstance() {
        if (null == dbInstance) {
            dbInstance = new RegistrationDatabase();
        }
        return dbInstance;
    }

    public RegistrationDatabase(){

        try {
            if(ConnectionMyDataBase.dbConnection==null){
                ConnectionMyDataBase.makeConnection();
            }
            init();
        } catch (SQLException e) {
            System.out.println("Log - SQLexception");
        }

    }

    private boolean init() throws SQLException{
        

        if (ConnectionMyDataBase.dbConnection != null) {
            System.out.println("create table first");
            //创建表
            String createBasicDB = "CREATE TABLE IF NOT EXISTS registration (\n" +
                    " id integer PRIMARY KEY,\n"+
                    "username varchar(50),\n" +
                    "password varchar(50),\n" +
                    "email varchar(50)\n" +
                    ");";

            Statement createStatement = ConnectionMyDataBase.dbConnection.createStatement();
            createStatement.executeUpdate(createBasicDB);
            createStatement.close();
            System.out.println("Create table finished.");


            //Create database if no avaiable
            return true;
        }

        System.out.println("DB creation failed");
        return false;

    }

   

    public void storeRegistrations(Registration user) throws SQLException{

        //Code to store coordinates to database
        String setMessageString = "insert into registration(username,password,email) VALUES(?,?,?) ";

        try{
            PreparedStatement pstmt = ConnectionMyDataBase.dbConnection.prepareStatement(setMessageString);

            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getEmail());

            pstmt.executeUpdate();
            System.out.println("register success");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

    }
    public boolean hasUsername(String parameter)  throws SQLException {
        //Code to get coordinates from the database
        String sql = "SELECT * FROM registration where username = ? ";
        try {
            PreparedStatement preparedStatement = ConnectionMyDataBase.dbConnection.prepareStatement(sql);
            preparedStatement.setString(1, parameter);
            ResultSet rs = preparedStatement.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return false;
    }


    public String getRegistrationsByUsername(String parameter)  throws SQLException {
        //Code to get coordinates from the database
        String sql = "SELECT * FROM registration where username = ? ";
        try {
            PreparedStatement preparedStatement = ConnectionMyDataBase.dbConnection.prepareStatement(sql);
            preparedStatement.setString(1,parameter);
            ResultSet rs = preparedStatement.executeQuery();
            if(rs.next()) return rs.getString("password");
            return null;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return null;
    }


}